package flst.ipiweb.persistance.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import flst.ipiweb.persistance.interfaces.IRelaisDAL;
import flst.ipiweb.transverse.IPIWebException;
import flst.ipiweb.transverse.Relais;

public class RelaisJdbcDAL implements IRelaisDAL{


	public List<Relais> getAllRelais() throws IPIWebException {
		
		List<Relais> lrel = new ArrayList<Relais>();
		Connection maconnection;

		try {
			maconnection = JdbcUtils.getInstance().getConnection();
			Statement req = maconnection.createStatement();
			String requeteselect = "SELECT IDRELAIS, NOM, VILLE FROM RELAIS WHERE ISACTIF = TRUE";
			req.execute(requeteselect);

			ResultSet rs = req.getResultSet();
			while (rs.next()) {
					Relais rel = new Relais();
					rel.setId(rs.getInt(1));
					rel.setNom(rs.getString(2));
					rel.setVille(rs.getString(3));
					lrel.add(rel);
				
			}

			req.close();
			maconnection.close();
		} catch (Exception e) {
			throw new IPIWebException(e.getMessage());
		}
		return lrel;
	
	}


	public Relais getRelaisById(int id) throws IPIWebException {
		
		Connection maconnection;
		Relais rel = new Relais();
		
		try {
			maconnection = JdbcUtils.getInstance().getConnection();
			PreparedStatement req = maconnection.prepareStatement("SELECT IDRELAIS, NOM, ADRESSE, CPLTADRESSE, CODEPOSTAL, VILLE FROM RELAIS WHERE IDRELAIS = ? AND ISACTIF=TRUE;");
			req.setInt(1, id);
			req.execute();

			ResultSet rs = req.getResultSet();
			while (rs.next()) {
					rel.setId(rs.getInt(1));
					rel.setNom(rs.getString(2));
					rel.setAdresse(rs.getString(3));
					rel.setCpltAdresse(rs.getString(4));
					rel.setCodePostal(rs.getInt(5));
					rel.setVille(rs.getString(6));
					
			}

			req.close();
			maconnection.close();
		} 
		catch (Exception e) {
			throw new IPIWebException(e.getMessage());
		}
		return rel;
	}


	public void CreateRelais(Relais rel) throws IPIWebException {
		
		Connection maconnection;
		
		try{
			maconnection = JdbcUtils.getInstance().getConnection();
			PreparedStatement req = maconnection.prepareStatement("INSERT INTO RELAIS (IDRELAIS, NOM, ADRESSE, CPLTADRESSE, CODEPOSTAL, VILLE, ISACTIF) VALUES ( ?, ?, ?, ?, ?, ?, TRUE );");
			req.setInt(1, rel.getId());
			req.setString(2, rel.getNom());
			req.setString(3, rel.getAdresse());
			req.setString(4, rel.getCpltAdresse());
			req.setInt(5, rel.getCodePostal());
			req.setString(6, rel.getVille());
			req.execute();
			
			req.close();
			maconnection.close();
		}
		catch(Exception e){
			throw new IPIWebException(e.getMessage());
		}
	}

	
	public void UpdateRelais(Relais rel) throws IPIWebException {
		
		Connection maconnection;
		
		try{
			maconnection = JdbcUtils.getInstance().getConnection();
			PreparedStatement req = maconnection.prepareStatement("UPDATE RELAIS SET NOM = ?, ADRESSE = ?, CPLTADRESSE = ?, CODEPOSTAL= ?, VILLE= ? WHERE IDRELAIS = ? AND ISACTIF = TRUE;");
			req.setString(1, rel.getNom());
			req.setString(2, rel.getAdresse());
			req.setString(3, rel.getCpltAdresse());
			req.setInt(4, rel.getCodePostal());
			req.setString(5, rel.getVille());
			req.setInt(6, rel.getId());
			req.execute();
			
			req.close();
			maconnection.close();
		}
		catch(Exception e){
			throw new IPIWebException(e.getMessage());
		}
	}
	
	public int GenerateId() throws IPIWebException{
		
		int id=0;
		Connection maconnection;
		
		try {
			maconnection = JdbcUtils.getInstance().getConnection();
			Statement req = maconnection.createStatement();
			String requeteselect = "SELECT SEQ_RELAIS.NEXTVAL;";
			req.execute(requeteselect);

			ResultSet rs = req.getResultSet();
			while (rs.next()) {
					id=rs.getInt(1);
			}

			req.close();
			maconnection.close();
		} 
		catch (Exception e) {
			throw new IPIWebException(e.getMessage());
		}
		
		return id;
	}



	public void DesactiverRelais(int id) throws IPIWebException {

			Connection maconnection;

			try {
				maconnection = JdbcUtils.getInstance().getConnection();
				PreparedStatement req = maconnection.prepareStatement("UPDATE RELAIS SET ISACTIF = FALSE WHERE IDRELAIS=? AND ISACTIF = TRUE;");
				req.setInt(1, id);
				req.execute();
				req.close();
				maconnection.close();
			} catch (Exception e) {
				throw new IPIWebException(e.getMessage());
			}
		
		
	}

}
